﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human.FormBaoCao
{
    public partial class frmbc_dsnvthamgiabaohiem : Form
    {
        string s_bophan="",user_dn="", sql = "", tungay = "", denngay = "", s_mmyy = "";
        DataSet ds = new DataSet();
        AccessData mDB = new AccessData();
        Language lan = new Language();
        public frmbc_dsnvthamgiabaohiem(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }

        private void frmbc_dsnvthamgiabaohiem_Load(object sender, EventArgs e)
        {

        }
        private DataSet getdata()
        {
            getngay(); //bh = chkl_loaibh.CheckedItems.ToString();
            sql = " select l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,'' as ghichu,'' as stbh";
            sql += " ,case when l.phai='Nam' then to_char(l.ngaysinh,'dd/mm/yyyy') else '' end as ngays_nam";
            sql += " ,case when l.phai<>'Nam' then to_char(l.ngaysinh,'dd/mm/yyyy') else '' end as ngays_nu";
            sql += " ,trim(l.diachi)||' - '||trim(qu.tenquan)||' - '||trim(tt.tentt) as diachi_tt";
            sql += " ,trim(l.noio)||' - '||trim(qu_n.tenquan)||' - '||trim(tt_n.tentt) as noio";
            sql += " ,to_char(hd.ngaybhxh,'dd/mm/yyyy')as ngaybh";
            sql += " ,case when hd.bhxh=1 then 'X' else '' end as bhxh";
            sql += " ,case when hd.bhyt=1 then 'X' else '' end as bhyt";
            sql += " ,case when hd.bhtn=1 then 'X' else '' end as bhtn";
            sql += " ,t_bh.tentt as tinh_bhxh,hd.sobhxh as sosobhxh,to_char(l.ngaycap,'dd/mm/yyyy') as ngaycap_sobh,hd.k_tinhbhxh as st_khongdongbh";
            sql += " ,l.madv,bp.tenbp,to_char(hd.ngaydenct,'dd/mm/yyyy') as ngaydenct,to_char(hd.ngaydenct,'yyyy') as namdenct";
            sql += " ,EXTRACT(month from age(to_timestamp('"+ denngay +"','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy'))) as sothangbh";
            sql += " ,(EXTRACT(year from age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))) as sonambh";
            sql += " from "+mDB.user+".llnv l inner join "+mDB.user+".hopdong hd on l.manv=hd.manv";

            sql += " left join " + mDB.user + ".btdtt tt_n on l.t_noio=tt_n.matt";
            sql += " left join " + mDB.user + ".btdtt tt on l.t_diachi=tt.matt";
            sql += " left join " + mDB.user + ".btdquan qu_n on l.h_noio=qu_n.maqu";
            sql += " left join " + mDB.user + ".btdquan qu on l.h_diachi=qu.maqu";
            sql += " left join " + mDB.user + ".btdtt t_bh on l.matinhbhxh=t_bh.matt";
            sql += " left join " + mDB.user + ".bophan bp on l.madv=bp.madv";
            sql += " left join " + mDB.user + ".max_lan ml on hd.manv=ml.manv";
            sql +=" where hd.lan=ml.lan";           
            if (chkl_loaibh.SelectedIndex == 0)
                sql += " and hd.bhxh=1 or hd.bhyt=1 or hd.bhtn=1";
            if (chkl_loaibh.SelectedIndex == 1)              
                sql += " and hd.bhxh=1";
            if (chkl_loaibh.SelectedIndex == 2)
                sql += " and hd.bhyt=1";
            if (chkl_loaibh.SelectedIndex == 3)
                sql += " and hd.bhtn=1";
            ds = mDB.get_data(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int nambh = 0, thangbh = 0;
                nambh = dr["sonambh"].ToString().Trim() !=""? int.Parse(dr["sonambh"].ToString().Trim()):0;
                thangbh = dr["sothangbh"].ToString().Trim()!=""? int.Parse(dr["sothangbh"].ToString().Trim()):0;              
               
                if (nambh > 0)
                {
                    dr["sonambh"] = nambh.ToString() ;
                }
                if (thangbh > 0)
                {
                    dr["sothangbh"] = thangbh.ToString() ;
                }
            }
            return ds;//mDB.get_data(sql);
        }
        private void getngay()
        {
            tungay = haison1.tungay;
            denngay = haison1.denngay;
            s_mmyy = denngay.Substring(3, 2) + denngay.Substring(8,2);
        }
        private void print(int prn)
        {
            DataSet dstmp = new DataSet();
            ExportToExcel exp = new ExportToExcel();
            ds = getdata();
            ds.WriteXml("..\\xml\\h_dsnvthamgiabaohiem.xml", XmlWriteMode.WriteSchema);
           
            if (ds.Tables[0].Rows.Count > 0)
            {
                switch (prn)
                {
                    case 1: frmReport frm = new frmReport(mDB, ds, lan.Change_language_MessageText("DANH SÁCH CÁN BỘ THAM GIA BHXH, BHYT "), "h_dsnvthamgiabaohiem.rpt",user_dn);
                        frm.ShowDialog();
                        break;
                    case 2: dstmp = xuat_exel(ds);
                        exp.expbc_dscbthamgiabh(dstmp, "dsnvthamgiabhxhbhyt", "", "");
                        break;
                }
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu"),AccessData.Msg, MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        private DataSet xuat_exel(DataSet dst)
        {
            DataSet ds1 = new DataSet();
            ds1.Tables.Add();
            ds1.Tables[0].Columns.Add("stt", typeof(string));
            ds1.Tables[0].Columns.Add("hoten", typeof(string));
            
            ds1.Tables[0].Columns.Add("ngays_nam", typeof(string));
            ds1.Tables[0].Columns.Add("ngays_nu", typeof(string));
            ds1.Tables[0].Columns.Add("namdenct", typeof(string));            
            ds1.Tables[0].Columns.Add("sosobhxh", typeof(string));
            ds1.Tables[0].Columns.Add("stbh", typeof(string));
          
            ds1.Tables[0].Columns.Add("ghichu", typeof(string));

            DataRow nrow;
            int stt = 0;
            foreach (DataRow dr in dst.Tables[0].Rows)
            {
                string stbh = "";
                stt++;
                nrow = ds1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["hoten"] = dr["hoten"].ToString();
                nrow["ngays_nam"] = dr["ngays_nam"].ToString();
                nrow["ngays_nu"] = dr["ngays_nu"].ToString();

                nrow["namdenct"] = dr["namdenct"].ToString();
                nrow["sosobhxh"] = dr["sosobhxh"].ToString();
                stbh = dr["sonambh"].ToString() + " năm " + dr["sothangbh"].ToString() + " tháng ";
                nrow["stbh"] = stbh;
                nrow["ghichu"] = dr["ghichu"].ToString();
                ds1.Tables[0].Rows.Add(nrow);                
            }
            return ds1;
        }
        private void btn_xem_Click(object sender, EventArgs e)
        {
           print(1);
          
        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_excel_Click(object sender, EventArgs e)
        {
            print(2);
        }
      
    }
}